﻿using CommonLib;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YouErYuanModels;

namespace YouErYuanBLL
{
    public class ModelReportBLL
    {
        /// <summary>
        /// 查询模块统计
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="vision"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable modelReport(int pageIndex, int pageSize, string startTime, string endTime, string vision, out int total)
        {
            //开始时间
            startTime = startTime == "" ? "" : " and clickTime>='" + startTime + "' ";
            //结束时间
            endTime = endTime == "" ? "" : " and clickTime<='" + endTime + " 23:59" + "' ";
            //子段ID
            string IDs = ".jzid";
            //园丁或家长
            string t_jiazhang = "t_jiazhang";
            if (vision == "2")
            {
                t_jiazhang = "T_Teacher";
                IDs = ".id";
            }
            string sqlZiduan0 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.校园新闻), vision);
            string sqlZiduan1 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.通知活动), vision);
            string sqlZiduan2 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.宝贝课程), vision);
            string sqlZiduan3 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.签到代接), vision);
            string sqlZiduan4 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.宝宝食谱), vision);
            string sqlZiduan5 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.宝宝视频), vision);
            string sqlZiduan6 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.校园简介), vision);
            string sqlZiduan7 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.互动留言), vision);
            string sqlZiduan8 = GetSqls(startTime, endTime, IDs, t_jiazhang, (int)(ModelsEnum.成长档案), vision);
            string sql = " (select  a.schoolName ,a. schoolId";
            sql += sqlZiduan0;
            sql += sqlZiduan1;
            sql += sqlZiduan2;
            sql += sqlZiduan3;
            sql += sqlZiduan4;
            sql += sqlZiduan5;
            sql += sqlZiduan6;
            sql += sqlZiduan7;
            sql += sqlZiduan8;
            sql += " from T_School a ) s";
            DataTable dt = SQLHepler.GetPaging(" a1 desc", sql, "", pageIndex, pageSize, out total);
            return dt;
        }
        /// <summary>
        /// 得到相应的sql拼接语句
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="IDs"></param>
        /// <param name="t_jiazhang"></param>
        /// <param name="ModelName"></param>
        /// <returns></returns>
        private string GetSqls(string startTime, string endTime, string IDs, string t_jiazhang, int ModelName, string vtype)
        {
            string sqlZiDuan = "";
            sqlZiDuan += " ,( ";
            sqlZiDuan += " select count(modelname)  from T_ModelsReport e ";
            sqlZiDuan += " left join " + t_jiazhang + " f on e.loginID=f" + IDs + " ";
            sqlZiDuan += " where f.schoolId=a.schoolId  and modelName='" + ModelName + "' ";
            sqlZiDuan += " and loginType=" + vtype + " " + startTime + endTime + " ";
            sqlZiDuan += " group by modelname ";
            sqlZiDuan += " )as  'a" + ModelName + "' ";
            return sqlZiDuan;
        }

        /// <summary>
        /// 得到图表数据
        /// </summary>
        /// <returns></returns>
        public DataTable modelChars(string schoollID, string startTime, string endTime, string vision)
        {
            string star = startTime;
            StringBuilder sql = new StringBuilder();
            sql.Append(" declare @star datetime='" + star + "' ");
            sql.Append(" declare @end datetime=dateadd(hh,24,@star) ");
            sql.Append(" declare @shcool varchar(50)='" + schoollID + "' ");
            sql.Append(" if object_id(N'#dayData',N'U') is  null ");
            sql.Append(" begin ");
            sql.Append(" create table #dayData(days datetime, a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int)");
            sql.Append(" end ");
            sql.Append(" while datediff(hh,@star,@end)>=0 ");
            sql.Append(" begin ");
            sql.Append(" insert into #dayData select @star as days,");
            sql.Append(getSqls((int)(ModelsEnum.校园新闻), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.通知活动), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.宝贝课程), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.签到代接), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.宝宝食谱), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.宝宝视频), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.校园简介), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.互动留言), vision) + " , ");
            sql.Append(getSqls((int)(ModelsEnum.成长档案), vision) + ";");
            sql.Append(" set @star=DATEADD(hh,1,@star); ");
            sql.Append(" end ");
            sql.Append(" select *from #dayData;  ");
            return SQLHepler.GetDataTable(sql.ToString());
        }
        /// <summary>
        /// 拼接sql
        /// </summary>
        /// <returns></returns>
        public string getSqls(int modelname, string vision)
        {
            string table = " t_jiazhang b on a.loginID = b.jzId ";
            if (vision == "2")
            {
                table = " T_Teacher b on a.loginID = b.id ";
            }
            string sql = @" (select count(1) from T_ModelsReport a  left join " + table;
                  sql += @" left join T_School c on b.schoolid = c.schoolid
                            where b.schoolid = @shcool and datediff(hh, @star, a.clickTime)= 0
                            and modelname = '" + modelname + "' and logintype =" + vision + ")";
            return sql;
        }
    }
}
